package TeacherEvaluation.com.service;

import java.io.FileInputStream;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import TeacherEvaluation.com.dao.standardDao;
import TeacherEvaluation.com.entity.Mark;

@Service
public class readExcel {
	@Autowired
	private standardDao standarddao;
	
	public int readXLSX(String filename) {
		//System.out.println(filename);
        InputStream is = null;
        XSSFWorkbook xssfWorkbook = null;
        try {
            // 1 打开Excel文件
            is = new FileInputStream(filename);

            // 2 通过POI的工作薄类解析xls文件
            xssfWorkbook = new XSSFWorkbook(is);

        } catch (Exception e) {
            is = null;
            e.printStackTrace();
        }

        if (is == null)
            return 0;

        // 3 创建people对象准备接收数据
        //People people = new People();
        Mark mark = new Mark();

        // 4 循环工作表Sheet，一个excel中可能有多个sheet
        for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
            if (xssfSheet == null) {
                continue;
            }
            // 循环行Row,一个sheet中可以有多行
            for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {

                // 取得当前行的数据
                XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                if (xssfRow != null) {

                    // 获取单元格对象
                    XSSFCell ma_type = xssfRow.getCell(0);
                    XSSFCell ma_kind = xssfRow.getCell(1);
                    XSSFCell ma_score = xssfRow.getCell(2);
                    XSSFCell optionA = xssfRow.getCell(3);
                    XSSFCell optionB = xssfRow.getCell(4);
                    XSSFCell optionC = xssfRow.getCell(5);
                    XSSFCell optionD = xssfRow.getCell(6);
                    XSSFCell ma_order = xssfRow.getCell(7);

                    // 全部先作为字符串数据提取出来
                    String ma_type_1 = getXValue(ma_type);
                    String ma_kind_1 = getXValue(ma_kind);
                    String ma_score_1 = getXValue(ma_score);
                    String optionA_1 = getXValue(optionA);
                    String optionB_1 = getXValue(optionB);
                    String optionC_1 = getXValue(optionC);
                    String optionD_1 = getXValue(optionD);
                    String ma_order_1 = getXValue(ma_order);

                    
                        // 转换并赋值给mark
                    	mark.setMa_type(ma_type_1);
                    	mark.setMa_kind(ma_kind_1);
                    	mark.setMa_score(Double.parseDouble(ma_score_1));
                    	double tmp =Double.parseDouble(ma_order_1);
                    	mark.setMa_order((int)tmp);
                    	mark.setA(optionA_1);
                    	mark.setB(optionB_1);
                    	mark.setC(optionC_1);
                    	mark.setD(optionD_1);
                    	standarddao.insertStandard(mark);
                   
                    	
                } // 结束不为空的行的处理
            } // 结束循环处理row
        } // 结束循环处理sheet

        // 关闭EXCEL表
        try {
            xssfWorkbook.close();
        } catch (Exception e) {

        }

        return 1;
    }

//对应版本读取数据的函数
private String getXValue(XSSFCell xssfCell) {

		switch(xssfCell.getCellType()) {
	    case STRING:
	    	return String.valueOf(xssfCell.getStringCellValue());
		case NUMERIC:
	        return String.valueOf(xssfCell.getNumericCellValue());
	    case BOOLEAN:
	    	return "";
	    case FORMULA:
	    	return "";
	    case BLANK:
	    	return "";
		default:
			break;
		}
		return null;
	 
    }

	

}
